﻿CREATE PROCEDURE [dbo].[proc_Project_Getlist_xfml_xx]
	(
		@CompanyId int,
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@Audit int,
		@Stext nvarchar(50),
		@StartIndex int,
		@EndIndex INT,
		@TypeId INT,
		
		@OId uniqueidentifier
	)
AS
Begin
--	;WITH list as (
		
--		SELECT ROW_NUMBER() OVER (ORDER BY cusname)AS Row	,* FROM (
--		Select id,ProjectId
--		,OrderId
--		,CusName
--		,ProductName
--		,SupplierName
--		,CostPrice
--		,TypeName
--		,IsFinish
--		,SalePrice
--		,ISNULL(FinishDate,'1900-01-01') As FinishDate
--		,FinanceAudit
--		,ISNULL(FinanceAuditDate,'1900-01-01') As FinanceAuditDate
--		,Salesman
--		,CreateDate
--		,(SalePrice-CostPrice) AS a
--		From Project
-- WHERE TypeId= @TypeId  and ( CompanyId=@CompanyId) and Convert(nchar(10),CreateDate,120) between @StartDate and @EndDate
--		and SalePrice<>CostPrice AND OrderId=@OId   AND ProductId not IN (22,29,33) AND (NOE_Flag<>2)
--	--AND (NOE_Flag<>2)	AND   oid IN (SELECT oid FROM [Order] o WHERE companyId=@CompanyId AND o.NOE_Flag<>2 )
--UNION 
--SELECT 0,OId,OId,CusName,'','', 0 ,'',Convert(bit,0),
--(CASE @TypeId WHEN 1 then  dch_xzml ELSE dch_xfml END),'1900-01-01',0,'1900-01-01','',UpTime,
--(CASE @TypeId WHEN 1 then  dch_xzml ELSE dch_xfml END)
--  FROM Order_Change_Hedge WHERE CompanyId=@companyId and Convert(nchar(10),UpTime,120) between @StartDate and @EndDate
--  AND 	(CASE @TypeId WHEN 1 then  dch_xzml ELSE dch_xfml END)!=0 
--	AND OId=@OId 	AND   oid IN (SELECT oid FROM [Order] o WHERE companyId=@CompanyId AND o.NOE_Flag<>2 )
--		) AS ab
--	)
		
--	Select *,(Select Count(1) From list) AS DataCount
--		,(Select Sum(a) From list) AS CostPrice_SUM 
----(Select Sum(SalePrice-CostPrice) From Project WHERE TypeId= @TypeId  and ( CompanyId=@CompanyId) and Convert(nchar(10),CreateDate,120) between @StartDate and @EndDate) AS CostPrice_SUM 	
--	From list Where Row between @StartIndex and @EndIndex  


	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime, @StartDate + ' 00:00:00')
	Set @eRq = Convert(Datetime, @EndDate + ' 23:59:59')

	;WITH list as (
		
		SELECT ROW_NUMBER() OVER (ORDER BY cusname)AS Row	,* FROM (
		Select id,ProjectId
		,OrderId
		,CusName
		,ProductName
		,SupplierName
		,CostPrice
		,TypeName
		,IsFinish
		,SalePrice
		,ISNULL(FinishDate,'1900-01-01') As FinishDate
		,FinanceAudit
		,ISNULL(FinanceAuditDate,'1900-01-01') As FinanceAuditDate
		,Salesman
		,CreateDate
		,(SalePrice-CostPrice) AS a
		From Project
 WHERE TypeId= @TypeId  and ( CompanyId=@CompanyId) and CreateDate >= @sRq and CreateDate<=@eRq
		and SalePrice<>CostPrice AND OrderId=@OId   AND ProductId not IN (22,29,33) AND (NOE_Flag<>2)
	--AND (NOE_Flag<>2)	AND   oid IN (SELECT oid FROM [Order] o WHERE companyId=@CompanyId AND o.NOE_Flag<>2 )
UNION 
SELECT 0,Order_Change_Hedge.OId,Order_Change_Hedge.OId,Order_Change_Hedge.CusName,'','', 0 ,'',Convert(bit,0),
(CASE @TypeId WHEN 1 then  dch_xzml ELSE dch_xfml END),'1900-01-01',0,'1900-01-01','',UpTime,
(CASE @TypeId WHEN 1 then  dch_xzml ELSE dch_xfml END)
  FROM Order_Change_Hedge inner join [Order] o on Order_Change_Hedge.oid =o.oid and Order_Change_Hedge.CompanyId=@companyId and UpTime>= @sRq and UpTime<=@eRq
  AND 	(CASE @TypeId WHEN 1 then  dch_xzml ELSE dch_xfml END)!=0 
	AND Order_Change_Hedge.OId=@OId 	AND   o.companyId=@CompanyId AND o.NOE_Flag<>2 
		) AS ab
	)
		
	Select *,(Select Count(1) From list) AS DataCount
		,(Select Sum(a) From list) AS CostPrice_SUM 
--(Select Sum(SalePrice-CostPrice) From Project WHERE TypeId= @TypeId  and ( CompanyId=@CompanyId) and Convert(nchar(10),CreateDate,120) between @StartDate and @EndDate) AS CostPrice_SUM 	
	From list Where Row between @StartIndex and @EndIndex  
End
	RETURN
